CREATE VIEW [dbo].[vOpportunityTaskUser]
AS
SELECT [dbo].[TaskItem].[OpportunityKey], [dbo].[GroupMember].[MemberContactKey] as UserKey, [dbo].[TaskItem].[Subject], [dbo].[TaskItem].[Category], [dbo].[TaskItemStatusRef].[TaskItemStatusName], [dbo].[TaskItem].[StartDate], [dbo].[TaskItem].[DueDate], [dbo].[TaskItem].[TaskItemKey]
FROM [dbo].[TaskItem]
    INNER JOIN [dbo].[OpportunityMain] ON
        [dbo].[TaskItem].[OpportunityKey] = [dbo].[OpportunityMain].[OpportunityKey]
    INNER JOIN [dbo].[GroupMemberDetail] ON
        [dbo].[TaskItem].[AssignedToKey] = [dbo].[GroupMemberDetail].[GroupRoleKey]
    INNER JOIN [dbo].[GroupMember] ON
        [dbo].[GroupMemberDetail].[GroupMemberKey] = [dbo].[GroupMember].[GroupMemberKey] AND
        [dbo].[OpportunityMain].[OpportunityOwnerGroupKey] = [GroupMember].[GroupKey]
    INNER JOIN [dbo].[TaskItemStatusRef] ON
        [dbo].[TaskItem].[TaskItemStatusCode] = [dbo].[TaskItemStatusRef].[TaskItemStatusCode]
UNION
SELECT [dbo].[TaskItem].[OpportunityKey], [dbo].[UserToken].[UserKey], [dbo].[TaskItem].[Subject], [dbo].[TaskItem].[Category], [dbo].[TaskItemStatusRef].[TaskItemStatusName], [dbo].[TaskItem].[StartDate], [dbo].[TaskItem].[DueDate], [dbo].[TaskItem].[TaskItemKey]
FROM [dbo].[TaskItem]
    INNER JOIN [dbo].[UserToken] ON
        [dbo].[UserToken].[Grantee] = [dbo].[TaskItem].[AssignedToKey]
    INNER JOIN [dbo].[TaskItemStatusRef] ON
        [dbo].[TaskItem].[TaskItemStatusCode] = [dbo].[TaskItemStatusRef].[TaskItemStatusCode]

GO